package org.kernelab.basis.apps;

import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JScrollPane;
import javax.swing.JSplitPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.JToggleButton;
import javax.swing.JToolBar;
import javax.swing.JTree;
import javax.swing.border.TitledBorder;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.TreePath;

import org.kernelab.basis.Tools;
import org.kernelab.basis.gui.VectorObjects;
import org.kernelab.basis.gui.table.ListTable;
import org.kernelab.basis.sql.DataBase;
import org.kernelab.basis.sql.DataBase.MySQL;
import org.kernelab.basis.sql.SQLKit;

public class MySQLAnalyzer extends JFrame
{

	/**
	 * 
	 */
	private static final long	serialVersionUID	= -3192622971146806664L;

	static {
		Tools.configLookAndFeel();
	}

	/**
	 * @param args
	 */
	public static void main(String[] args)
	{
		new MySQLAnalyzer();
	}

	public static void showResultSet(ListTable<VectorObjects> table, ResultSet rs)
	{
		try {

			ResultSetMetaData meta = rs.getMetaData();

			int columns = meta.getColumnCount();

			List<Object> header = new Vector<Object>();

			for (int i = 0; i < columns; i++) {
				header.add(meta.getColumnName(i + 1));
			}

			table.setHeader(header);

			table.refreshHeader();

			table.getData().clear();

			while (rs.next()) {
				Object[] cells = new String[columns];

				for (int i = 0; i < columns; i++) {
					cells[i] = rs.getString(i + 1);
				}

				table.getData().add(new VectorObjects(cells));
			}

			table.refreshData();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void showResultSetTranspose(ListTable<VectorObjects> table, ResultSet rs)
	{
		try {

			int columns = SQLKit.getResultNumber(rs) + 1;

			List<Object> header = new Vector<Object>();

			header.add("Item");

			for (int i = 1; i < columns; i++) {
				header.add("Data " + i);
			}

			table.setHeader(header);

			table.refreshHeader();

			table.getData().clear();

			ResultSetMetaData meta = rs.getMetaData();

			int rows = meta.getColumnCount();

			ArrayList<VectorObjects> data = new ArrayList<VectorObjects>(rows);

			for (int i = 0; i < rows; i++) {

				VectorObjects vo = new VectorObjects(columns);

				vo.vectorAccess(0, meta.getColumnName(i + 1));

				data.add(vo);
			}

			while (rs.next()) {

				for (int i = 0; i < rows; i++) {
					data.get(i).vectorAccess(rs.getRow(), rs.getString(i + 1));
				}

			}

			table.getData().addAll(data);

			table.refreshData();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private DataBase					dataBase;

	private SQLKit						kit;

	private JTextField					userNameField;

	private JPasswordField				passWordField;

	private JButton						logonButton;

	private JPanel						logonPanel;

	private DefaultMutableTreeNode		dataBases;

	private JTree						dataBaseBrowser;

	private JScrollPane					dataBaseBrowserPane;

	private JPanel						leftPanel;

	private JTextArea					sqlArea;

	private JScrollPane					sqlAreaPane;

	private JButton						sqlButton;

	private JPanel						sqlPanel;

	private ListTable<VectorObjects>	browserTable;

	private JScrollPane					browserPane;

	private boolean						explaining;

	private ListTable<VectorObjects>	explainTable;

	private JScrollPane					explainPane;

	private JSplitPane					browserExplainPane;

	private boolean						profiling;

	private ListTable<VectorObjects>	profileTable;

	private JScrollPane					profilePane;

	private JSplitPane					browserProfilePane;

	private JSplitPane					mainPanel;

	private JSplitPane					frame;

	private JToolBar					statusBar;

	private JToggleButton				showExplainButton;

	private JToggleButton				showProfileButton;

	private JLabel						durationLabel;

	private JTextArea					errorMessage;

	private JScrollPane					errorMessagePane;

	public MySQLAnalyzer()
	{
		super("MySQL Analyzer - KerneLab.org");

		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

		this.userNameField = new JTextField();

		this.passWordField = new JPasswordField();

		this.logonButton = new JButton("Logon");

		this.logonPanel = new JPanel();

		this.dataBases = new DefaultMutableTreeNode("MySQL DataBase");

		this.dataBaseBrowser = new JTree(dataBases);

		this.dataBaseBrowserPane = new JScrollPane(dataBaseBrowser);

		this.leftPanel = new JPanel();

		this.sqlArea = new JTextArea();

		this.sqlAreaPane = new JScrollPane(sqlArea);

		this.sqlButton = new JButton("SQL");

		this.sqlPanel = new JPanel();

		this.browserTable = new ListTable<VectorObjects>();

		this.browserPane = new JScrollPane(browserTable.getTable());

		this.explaining = true;

		this.explainTable = new ListTable<VectorObjects>();

		this.explainPane = new JScrollPane(explainTable.getTable());

		this.browserExplainPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT,
				this.browserPane, this.explainPane);

		this.profiling = true;

		this.profileTable = new ListTable<VectorObjects>();

		this.profilePane = new JScrollPane(profileTable.getTable());

		this.browserProfilePane = new JSplitPane(JSplitPane.VERTICAL_SPLIT,
				this.browserExplainPane, this.profilePane);

		this.mainPanel = new JSplitPane(JSplitPane.VERTICAL_SPLIT, this.sqlPanel,
				this.browserProfilePane);

		this.frame = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT, this.leftPanel,
				this.mainPanel);

		this.statusBar = new JToolBar();

		this.showExplainButton = new JToggleButton("Explain");

		this.showProfileButton = new JToggleButton("Profile");

		this.durationLabel = new JLabel("0.0");

		this.errorMessage = new JTextArea();

		this.errorMessagePane = new JScrollPane(errorMessage);

		this.config();

		this.arrange();
	}

	private void arrange()
	{
		this.arrangeLeftPanel();

		this.arrangeMainPanel();

		this.arrangeStatusBar();

		this.setLayout(new GridBagLayout());

		GridBagConstraints gbc = Tools.makePreferredGridBagConstraints();

		this.add(this.getFrame(), gbc);

		gbc.gridy++;
		gbc.weighty = 0.0;
		this.add(this.getStatusBar(), gbc);

		this.pack();

		this.getBrowserProfilePane().setDividerLocation(1.0);
		this.getBrowserExplainPane().setDividerLocation(1.0);

		this.setVisible(true);
	}

	private void arrangeBrowserPanel()
	{
		this.getBrowserPane().setPreferredSize(new Dimension(0, 0));
		this.getProfilePane().setPreferredSize(new Dimension(0, 0));
		this.getExplainPane().setPreferredSize(new Dimension(0, 0));

		this.getBrowserPane().setBorder(new TitledBorder("Result"));
		this.getExplainPane().setBorder(new TitledBorder("Explain"));
		this.getProfilePane().setBorder(new TitledBorder("Profile"));
	}

	private void arrangeLeftPanel()
	{
		this.arrangeLogonPanel();

		this.getLeftPanel().setLayout(new GridBagLayout());

		GridBagConstraints gbc = Tools.makePreferredGridBagConstraints();

		gbc.weightx = 1.0;
		gbc.weighty = 0.0;
		this.getLeftPanel().add(this.getLogonPanel(), gbc);

		this.getDataBaseBrowserPane().setBorder(new TitledBorder("Catalog"));
		gbc.gridy++;
		gbc.weighty = 1.0;
		this.getLeftPanel().add(this.getDataBaseBrowserPane(), gbc);

		this.getLeftPanel().setPreferredSize(new Dimension(200, 0));
	}

	private void arrangeLogonPanel()
	{
		this.getLogonPanel().setLayout(new GridBagLayout());

		GridBagConstraints gbc = Tools.makePreferredGridBagConstraints();
		gbc.insets = new Insets(1, 1, 1, 1);

		gbc.weighty = 0.0;
		gbc.weightx = 0.0;
		this.getLogonPanel().add(new JLabel("Username:"), gbc);

		gbc.gridx++;
		gbc.weightx = 1.0;
		this.getLogonPanel().add(this.getUserNameField(), gbc);

		gbc.gridy++;
		gbc.gridx = 0;
		gbc.weightx = 0.0;
		this.getLogonPanel().add(new JLabel("Password:"), gbc);

		gbc.gridx++;
		gbc.weightx = 1.0;
		this.getLogonPanel().add(this.getPassWordField(), gbc);

		gbc.gridy++;
		gbc.gridx = 0;
		gbc.gridwidth = 2;
		this.getLogonPanel().add(this.getLogonButton(), gbc);

		this.getLogonPanel().setBorder(new TitledBorder("Logon"));
	}

	private void arrangeMainPanel()
	{
		this.arrangeSQLPanel();

		this.arrangeBrowserPanel();

		this.getMainPanel().setPreferredSize(new Dimension(600, 600));
	}

	private void arrangeSQLPanel()
	{
		this.getSqlPanel().setLayout(new GridBagLayout());

		GridBagConstraints gbc = Tools.makePreferredGridBagConstraints();

		gbc.weightx = 1.0;
		gbc.weighty = 1.0;
		this.getSqlPanel().add(this.getSqlAreaPane(), gbc);

		this.getSqlButton().setPreferredSize(new Dimension(63, 63));
		gbc.gridx++;
		gbc.weightx = 0.0;
		this.getSqlPanel().add(this.getSqlButton(), gbc);

		this.getSqlPanel().setBorder(new TitledBorder("SQL"));
	}

	private void arrangeStatusBar()
	{
		this.getStatusBar().setFloatable(false);

		this.getStatusBar().add(this.getShowExplainButton());
		this.getStatusBar().addSeparator();
		this.getStatusBar().add(this.getShowProfileButton());
		this.getStatusBar().addSeparator();
		this.getStatusBar().add(new JLabel("Duration "));
		this.getStatusBar().add(this.getDurationLabel());
		this.getStatusBar().add(new JLabel("s"));
		this.getStatusBar().addSeparator();
		this.getStatusBar().add(this.getErrorMessagePane());
	}

	private void clickTable(DefaultMutableTreeNode tableNode)
	{
		this.getDataBase().setCatalog(
				((DefaultMutableTreeNode) tableNode.getParent()).getUserObject()
						.toString());

		String table = tableNode.getUserObject().toString();

		this.getSqlArea().setText(
				"SELECT * FROM `" + table + "` AS `"
						+ String.valueOf(table.charAt(0)).toLowerCase() + "`");

		this.getSqlArea().grabFocus();
	}

	private void config()
	{
		this.getFrame().setContinuousLayout(true);
		this.getMainPanel().setContinuousLayout(true);
		this.getBrowserExplainPane().setContinuousLayout(true);
		this.getBrowserProfilePane().setContinuousLayout(true);

		this.getSqlAreaPane().setHorizontalScrollBarPolicy(
				JScrollPane.HORIZONTAL_SCROLLBAR_NEVER);
		this.getSqlArea().setLineWrap(true);
		this.getSqlArea().setFont(new Font("Verdana", Font.PLAIN, 12));

		this.getErrorMessagePane().setHorizontalScrollBarPolicy(
				JScrollPane.HORIZONTAL_SCROLLBAR_NEVER);
		this.getErrorMessage().setLineWrap(true);
		this.getErrorMessage().setEditable(false);
		this.getErrorMessage().setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 11));
		this.getErrorMessage().setForeground(Color.RED);

		this.getSqlButton().setEnabled(false);

		this.getBrowserTable().getTable().setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		this.getExplainTable().getTable().setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		this.getProfileTable().getTable().setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

		this.getLogonButton().addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e)
			{
				logon();
			}

		});

		this.getDataBaseBrowser().addMouseListener(new MouseAdapter() {

			@Override
			public void mouseClicked(MouseEvent e)
			{
				TreePath path = getDataBaseBrowser().getSelectionPath();

				if (path != null) {

					Object obj = path.getLastPathComponent();

					if (obj instanceof DefaultMutableTreeNode) {

						DefaultMutableTreeNode node = (DefaultMutableTreeNode) obj;

						switch (e.getClickCount())
						{

							case 1:
								if (e.getButton() == MouseEvent.BUTTON3) {
									loadTables(node);
								}
								break;

							case 2:

								switch (node.getLevel())
								{
									case 1:
										loadTables(node);
										break;

									case 2:
										clickTable(node);
										break;
								}

								break;
						}
					}
				}
			}

		});

		this.getSqlArea().addKeyListener(new KeyAdapter() {

			@Override
			public void keyTyped(KeyEvent e)
			{
				if (e.isControlDown() && e.getKeyChar() == KeyEvent.VK_ENTER) {
					sql();
				}
			}

		});

		this.getSqlButton().addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e)
			{
				sql();
			}

		});

		this.getBrowserExplainPane().setResizeWeight(1.0);
		this.getBrowserProfilePane().setResizeWeight(1.0);

		this.getShowExplainButton().setFocusable(false);
		this.getShowExplainButton().setToolTipText("Show Explain");
		this.getShowExplainButton().addActionListener(new ActionListener() {

			int	location	= 180;

			@Override
			public void actionPerformed(ActionEvent e)
			{
				if (showExplainButton.isSelected()) {
					getBrowserExplainPane().setDividerLocation(
							getBrowserExplainPane().getWidth() - location);
				} else {
					location = getBrowserExplainPane().getWidth()
							- getBrowserExplainPane().getDividerLocation();
					getBrowserExplainPane().setDividerLocation(1.0);
				}

			}

		});

		this.getShowProfileButton().setFocusable(false);
		this.getShowProfileButton().setToolTipText("Show Profile");
		this.getShowProfileButton().addActionListener(new ActionListener() {

			int	location	= 180;

			@Override
			public void actionPerformed(ActionEvent e)
			{
				if (showProfileButton.isSelected()) {
					getBrowserProfilePane().setDividerLocation(
							getBrowserProfilePane().getHeight() - location);
				} else {
					location = getBrowserProfilePane().getHeight()
							- getBrowserProfilePane().getDividerLocation();
					getBrowserProfilePane().setDividerLocation(1.0);
				}
			}

		});

		this.getErrorMessage().addMouseListener(new MouseAdapter() {

			@Override
			public void mouseClicked(MouseEvent e)
			{
				if (e.getClickCount() == 2) {
					getErrorMessage().select(0, getErrorMessage().getText().length());
				}
			}

		});

	}

	public JSplitPane getBrowserExplainPane()
	{
		return browserExplainPane;
	}

	public JScrollPane getBrowserPane()
	{
		return browserPane;
	}

	public JSplitPane getBrowserProfilePane()
	{
		return browserProfilePane;
	}

	public ListTable<VectorObjects> getBrowserTable()
	{
		return browserTable;
	}

	public DataBase getDataBase()
	{
		return dataBase;
	}

	public JTree getDataBaseBrowser()
	{
		return dataBaseBrowser;
	}

	public JScrollPane getDataBaseBrowserPane()
	{
		return dataBaseBrowserPane;
	}

	public DefaultMutableTreeNode getDataBases()
	{
		return dataBases;
	}

	public JLabel getDurationLabel()
	{
		return durationLabel;
	}

	public JTextArea getErrorMessage()
	{
		return errorMessage;
	}

	public JScrollPane getErrorMessagePane()
	{
		return errorMessagePane;
	}

	public JScrollPane getExplainPane()
	{
		return explainPane;
	}

	public ListTable<VectorObjects> getExplainTable()
	{
		return explainTable;
	}

	public JSplitPane getFrame()
	{
		return frame;
	}

	public SQLKit getKit()
	{
		return kit;
	}

	public JPanel getLeftPanel()
	{
		return leftPanel;
	}

	public JButton getLogonButton()
	{
		return logonButton;
	}

	public JPanel getLogonPanel()
	{
		return logonPanel;
	}

	public JSplitPane getMainPanel()
	{
		return mainPanel;
	}

	public JPasswordField getPassWordField()
	{
		return passWordField;
	}

	public JScrollPane getProfilePane()
	{
		return profilePane;
	}

	public ListTable<VectorObjects> getProfileTable()
	{
		return profileTable;
	}

	public JToggleButton getShowExplainButton()
	{
		return showExplainButton;
	}

	public JToggleButton getShowProfileButton()
	{
		return showProfileButton;
	}

	public JTextArea getSqlArea()
	{
		return sqlArea;
	}

	public JScrollPane getSqlAreaPane()
	{
		return sqlAreaPane;
	}

	public JButton getSqlButton()
	{
		return sqlButton;
	}

	public JPanel getSqlPanel()
	{
		return sqlPanel;
	}

	public JToolBar getStatusBar()
	{
		return statusBar;
	}

	public JTextField getUserNameField()
	{
		return userNameField;
	}

	public boolean isExplaining()
	{
		return explaining;
	}

	public boolean isProfiling()
	{
		return profiling;
	}

	private void loadTables(DefaultMutableTreeNode dataBaseNode)
	{
		try {
			if (dataBaseNode.isLeaf()) {

				this.setKit(this.getDataBase().getSQLKit());

				ResultSet rs = this
						.getKit()
						.getConnection()
						.getMetaData()
						.getTables(dataBaseNode.getUserObject().toString(), null, "%",
								null);

				while (rs.next()) {
					DefaultMutableTreeNode table = new DefaultMutableTreeNode(
							rs.getString("TABLE_NAME"));

					dataBaseNode.add(table);
				}

				this.getDataBaseBrowser().fireTreeExpanded(
						new TreePath(dataBaseNode.getPath()));

			}

			this.getDataBase().setCatalog(dataBaseNode.getUserObject().toString());

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.getKit().close();
		}
	}

	private void logon()
	{
		String userName = this.getUserNameField().getText();
		String passWord = new String(this.getPassWordField().getPassword());

		this.setDataBase(new MySQL(userName, passWord));

		this.setKit(this.getDataBase().getSQLKit());

		try {
			DatabaseMetaData dbmd = this.getKit().getConnection().getMetaData();
			ResultSet rs = dbmd.getCatalogs();

			this.getDataBases().removeAllChildren();

			while (rs.next()) {
				DefaultMutableTreeNode database = new DefaultMutableTreeNode(
						rs.getString("TABLE_CAT"));

				this.getDataBases().add(database);
			}

			this.getDataBaseBrowser().fireTreeExpanded(new TreePath(this.getDataBases()));

			this.getSqlButton().setEnabled(true);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.getKit().close();
		}

	}

	private void putErrorMessage(String msg)
	{
		this.getErrorMessage().setText(msg);
		this.getErrorMessage().setCaretPosition(0);
	}

	private void setDataBase(DataBase dataBase)
	{
		this.dataBase = dataBase;
	}

	public void setExplaining(boolean explaining)
	{
		this.explaining = explaining;
	}

	private void setKit(SQLKit kit)
	{
		this.kit = kit;
	}

	public void setProfiling(boolean profiling)
	{
		this.profiling = profiling;
	}

	private void sql()
	{
		String sql = this.getSqlArea().getText().trim();
		sql = sql.replaceAll("\\r", "");
		sql = sql.replaceAll("\\n", "");

		this.setKit(this.getDataBase().getSQLKit());

		ResultSet rs = null;

		try {

			if (this.isProfiling()) {
				this.getKit().query("SET PROFILING = 1", SQLKit.EMPTY);
			}

			rs = this.getKit().query(sql, SQLKit.EMPTY);

			showResultSet(this.getBrowserTable(), rs);

			if (this.isExplaining()) {

				rs = this.getKit().query("EXPLAIN " + sql, SQLKit.EMPTY);

				showResultSetTranspose(this.getExplainTable(), rs);
			}

			if (this.isProfiling()) {

				rs = this.getKit().query("SHOW PROFILES", SQLKit.EMPTY);

				rs.next();

				this.getDurationLabel().setText(rs.getString(2));

				rs = this.getKit().query(
						"SHOW PROFILE cpu, memory, block io FOR QUERY 1", SQLKit.EMPTY);

				showResultSet(this.getProfileTable(), rs);
			}

		} catch (SQLException e) {
			this.putErrorMessage(e.getLocalizedMessage());
		} finally {
			this.getKit().close();
		}

	}

}
